//
//  CategoryDao.swift
//  KoiAccounting
//
//  Created by 纪洪文 on 20.03.25.
//

import Foundation
import GRDB

class CategoryDao {
    let dbQueue: DatabaseQueue
    
    init(dbQueue: DatabaseQueue) {
        self.dbQueue = dbQueue
    }
    
    func getCategoryByCid(cid: String, uid: String, bookId: String) -> AccountingCategory? {
        do {
            return try dbQueue.read { db in
                var category = try AccountingCategory
                    .filter(AccountingCategory.Columns.cid == cid)
                    .filter(AccountingCategory.Columns.uid == uid)
                    .filter(AccountingCategory.Columns.bookId == bookId)
                    .filter(AccountingCategory.Columns.isDel != 1)
                    .fetchOne(db)
                if category?.parentId == nil {
                    let subCategoryList = try AccountingCategory
                        .filter(AccountingCategory.Columns.uid == uid)
                        .filter(AccountingCategory.Columns.bookId == bookId)
                        .filter(AccountingCategory.Columns.isDel != 1)
                        .filter(AccountingCategory.Columns.parentId == category?.cid)
                        .fetchAll(db)
                    
                    category?.hasSubCategory = !subCategoryList.isEmpty
                }
                return category
            }
        } catch {
            logger.error("getCategoryByCid fail \(error)")
        }
        return nil
    }
    
    func getCategoryByType(uid:String, bookId:String, type: String) -> Array<AccountingCategory> {
        var result = Array<AccountingCategory>()
        do {
            result = try dbQueue.read{ db in
                // 先找到所有子类别
                let subList:Array<AccountingCategory> = try AccountingCategory
                    .filter(AccountingCategory.Columns.uid == uid)
                    .filter(AccountingCategory.Columns.bookId == bookId)
                    .filter(AccountingCategory.Columns.parentId != nil)
                    .filter(AccountingCategory.Columns.isDel != 1)
                    .fetchAll(db)
                
                var parentIdSet = Set<String>()
                for sub in subList {
                    if (sub.parentId != nil) {
                        parentIdSet.insert(sub.parentId!)
                    }
                }
                var categoryList = try AccountingCategory
                    .filter(AccountingCategory.Columns.uid == uid)
                    .filter(AccountingCategory.Columns.bookId == bookId)
                    .filter(AccountingCategory.Columns.type == type)
                    .filter(AccountingCategory.Columns.isDel != 1)
                    .filter(AccountingCategory.Columns.parentId == nil)
                    .order(AccountingCategory.Columns.orderId.asc)
                    .fetchAll(db)
                
                let cids = categoryList.map{it in (it.cid ?? "")}
                let argus = cids.map{ _ in "?"}.joined(separator: ",")
                let rows = try Row.fetchCursor(db, sql:"SELECT cid, COUNT(record_id) as count FROM \(AccountingDetail.databaseTableName) WHERE cid IN (\(argus)) group by cid", arguments: StatementArguments(cids))
                var categoryRecordMap = [String: Int]()
                while let row = try rows.next() {
                    let cid: String? = row["cid"]
                    let count: Int = row["count"]
                    if cid != nil {
                        categoryRecordMap[cid!] = count
                    }
                }
                
                for (index, category) in categoryList.enumerated() {
                    categoryList[index].hasSubCategory = parentIdSet.contains(category.cid!)
                    let recordCount = categoryRecordMap[category.cid!] ?? 0
                    categoryList[index].hasRecord = recordCount > 0
                }
                
                return categoryList
            }
        } catch {
            logger.error("getCategoryByType fail \(type) \(error)")
        }
        return result
    }
    
    func getSubCategoryListByParentId(uid:String, bookId:String, parentId:String) -> Array<AccountingCategory> {
        var result = Array<AccountingCategory>()
        do {
            result = try dbQueue.read { db in
                try AccountingCategory
                    .filter(AccountingCategory.Columns.uid == uid)
                    .filter(AccountingCategory.Columns.bookId == bookId)
                    .filter(AccountingCategory.Columns.parentId == parentId)
                    .filter(AccountingCategory.Columns.isDel != 1)
                    .order(AccountingCategory.Columns.orderId.asc)
                    .fetchAll(db)
            }
        } catch {
            logger.error("getCategoryByType fail \(error)")
        }
        return result
    }
    
    func getCategoryListByCids(uid:String, bookId:String, cids: Array<String>) -> Array<AccountingCategory> {
        var result = Array<AccountingCategory>()
        do {
            result = try dbQueue.read { db in
                try AccountingCategory
                    .filter(AccountingCategory.Columns.uid == uid)
                    .filter(AccountingCategory.Columns.bookId == bookId)
                    .filter(AccountingCategory.Columns.isDel != 1)
                    .filter(cids.contains(AccountingCategory.Columns.cid))
                    .fetchAll(db)
            }
        } catch {
            logger.error("getCategoryByType fail \(error)")
        }
        return result
    }
    
    func getSubCategoryListByParentCids(uid:String, bookId:String, cids: Array<String>) -> Array<AccountingCategory> {
        var result = Array<AccountingCategory>()
        do {
            result = try dbQueue.read { db in
                try AccountingCategory
                    .filter(AccountingCategory.Columns.uid == uid)
                    .filter(AccountingCategory.Columns.bookId == bookId)
                    .filter(AccountingCategory.Columns.isDel != 1)
                    .filter(cids.contains(AccountingCategory.Columns.parentId))
                    .fetchAll(db)
            }
        } catch {
            logger.error("getCategoryByType fail \(error)")
        }
        return result
    }
    
    func fetchHasRecordByCids(cids: [String]) -> [String: Int] {
        do {
            return try dbQueue.read { db in
                let rows = try Row.fetchCursor(db, sql:"SELECT cid, COUNT(*) FROM \(AccountingDetail.databaseTableName) WHERE cid IN (?)", arguments: [cids.joined(separator: ",")])
                var result = [String: Int]()
                while let row = try rows.next() {
                    let cid: String = row["cid"]
                    let count: Int = row["count"]
                    result[cid] = count
                }
                return result
            }
        } catch {
            return [String: Int]()
        }
    }
    
    func saveTest() {
        let date = Date().toISODateString()
        save(category: AccountingCategory(name: "测试", createTime: date))
    }
    
    // 获取最大的 orderId
    func fetchMaxOrderId() throws -> Int? {
        return try dbQueue.read { db in
            // 执行 SQL 查询来找到最大的 orderId
            let maxOrderId = try Int.fetchOne(db, sql: "SELECT MAX(order_id) FROM accounting_category")
            return maxOrderId
        }
    }
    
    func save(category: AccountingCategory) {
        var categoryDB = category
        if (categoryDB.cid == nil || categoryDB.cid!.isEmpty) {
            categoryDB.cid = AccountingCategory.generateCid()
        }
        // 保存之前需要找到最大的 orderId
        let maxOrderId = try? fetchMaxOrderId()
        categoryDB.orderId = (maxOrderId ?? 0) + 1
        do {
            try dbQueue.write { db in
                try categoryDB.insert(db)
            }
        } catch {
            logger.error("save category fail \(error)")
        }
    }
    
    func save(categorys : Array<AccountingCategory>) {
        do {
            try dbQueue.write { db in
                for var category in categorys {
                    try category.insert(db)
                }
            }
        } catch {
            logger.error("save categorys fail \(error)")
        }
    }
    
    func update(category: AccountingCategory) {
        var category = category
        category.makeUpdate()
        do {
            try dbQueue.write { db in
                try category.upsert(db)
            }
        } catch {
            logger.error("update category fail \(error)")
        }
    }
    
    func update(categorys: Array<AccountingCategory>) {
        do {
            try dbQueue.write { db in
                for category in categorys {
                    var category = category
                    category.makeUpdate()
                    try category.upsert(db)
                }
            }
        } catch {
            logger.error("update category fail \(error)")
        }
    }
    
    func getAllCategory() -> Array<AccountingCategory>? {
        do {
            let categorys = try dbQueue.read { db in
                try AccountingCategory.fetchAll(db)
            }
            return categorys
        } catch {
            logger.error("getAllCategory fail \(error)")
            return nil
        }
    }
    
    func deleateAllCategory(uid:String, bookId:String){
        do {
            return try dbQueue.write { db in
                try db.execute(sql: "delete from accounting_category where uid=? and book_id=?", arguments: [uid, bookId])
            }
        } catch {
            logger.error("delete accounting_category")
        }
    }
    
    func delete(category: AccountingCategory) {
        var category = category
        category.makeDelete()
        do {
            try dbQueue.write { db in
                try category.upsert(db)
            }
        } catch {
            logger.error("category delete")
        }
    }
    
    func getPushData(pageSize:Int, offset:Int, uid: String, bookId: String) -> Array<AccountingCategory> {
        do {
            return try dbQueue.read { db in
                try AccountingCategory
                    .filter(AccountingCategory.Columns.uid == uid)
                    .filter(AccountingCategory.Columns.bookId == bookId)
                    .filter(AccountingCategory.Columns.dataFlag != DataFlag.synced.rawValue)
                    .limit(pageSize, offset: offset)
                    .fetchAll(db)
            }
            
        } catch {
            logger.error("getPushData fail \(error)")
        }
        return [AccountingCategory]()
    }
    
    func updateSync(list: Array<AccountingCategory>) throws {
        try dbQueue.write { db in
            for category in list {
                var category = category
                try category.insert(db, onConflict: .replace)
            }
        }
    }
}
